package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.atsmart.app.model.ProductGroupInfo;
import com.atsmart.app.model.ProductGroupListInfo;

public class ProductGroupSqlHelper {

	private SQLiteDatabase db = null;

	/**
	 * 打开数据库
	 */
	public void open(SqlHelper dh) {
		// dh = new SqlHelper(null, "atsmart", null,SqlHelper.getDBVersion());
		db = dh.getWritableDatabase();
	}

	public synchronized void addProductGroupInfo(ProductGroupInfo at) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into productGroup_info (gid,name,parent_id,type) values(?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindLong(1, at.getId());
			stm.bindString(2, at.getName());
			stm.bindLong(3, at.getParent_id());
			stm.bindLong(4, at.getType());
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			// will not happen
		}
	}

	public ProductGroupListInfo getProductGroupInfo(int begin, int pagesize,
			int keyword) {
		String sql = " select _id,gid,name,parent_id,type from productGroup_info where parent_id ="
				+ keyword + " limit " + begin + "," + pagesize;
		Cursor cursor = db.rawQuery(sql, null);
		ProductGroupInfo historie = null;
		List<ProductGroupInfo> nums = null;
		ProductGroupListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ProductGroupInfo>();
				t = new ProductGroupListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ProductGroupInfo();
					historie.setId(cursor.getInt(1));
					historie.setName(cursor.getString(2));
					historie.setParent_id(cursor.getInt(3));
					historie.setType(cursor.getInt(4));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setProducts(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}

	public List<ProductGroupInfo> getProductGroupInfoList(int gid) {
		String sql = " select _id,gid,name,parent_id,type from productGroup_info where parent_id ="
				+ gid;
		Cursor cursor = db.rawQuery(sql, null);
		ProductGroupInfo historie = null;
		List<ProductGroupInfo> nums = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ProductGroupInfo>();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ProductGroupInfo();
					historie.setId(cursor.getInt(1));
					historie.setName(cursor.getString(2));
					historie.setParent_id(cursor.getInt(3));
					historie.setType(cursor.getInt(4));
					nums.add(historie);
					cursor.moveToNext();
				}
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return nums;
	}

	public String getProductGroupIds(ProductGroupInfo info) {
		StringBuffer sbf = new StringBuffer();
		if (info != null) {
			if (info.getType() == 1) {
				sbf.append(info.getId() + ",");
			} else {
				List<ProductGroupInfo> list = getProductGroupInfoList(info
						.getId());
				if (list != null) {
					for (ProductGroupInfo pinfo : list) {
						if (pinfo.getType() == 1) {
							sbf.append(pinfo.getId() + ",");
						} else {
							getProductGroupIds(pinfo);
						}
					}
				}

			}
		}
		if (sbf.length() > 0) {
			return sbf.substring(0, sbf.length() - 1).toString();
		}
		return null;

	}

	public int getCount() {
		String sql = " select _id,gid,name,parent_id,type from productGroup_info ";
		Cursor cursor = db.rawQuery(sql, null);
		try {
			if (cursor != null && cursor.moveToFirst()) {
				return cursor.getCount();
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		return 0;
	}

	public void deleteAttribute(int gid) {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from productGroup_info where gid =" + gid);
	}

	public void deleteProduct() {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from productGroup_info");
	}
}
